#!/usr/bin/env python
# coding=utf-8
"""外部数据使用报告
每天发送昨天的报告, 每月1号发送上月的报告"""
from datetime import datetime, timedelta
from sendmail import sendTextWithAttach
from MysqlUtil import query
import json
import csv

data_dir = "./data/"
sql_report = """
SELECT
	DATE_FORMAT( task.update_time, '%Y-%m-%d' ) update_time,
CASE
	ot.outer_datasource_type 
	WHEN 1 THEN
	'A40011' 
	WHEN 2 THEN
	'B10020' ELSE 'Wrong Datasource' 
	END AS 'datasource',
CASE
	ot.output_type 
	WHEN 0 THEN
	'常规标签' ELSE 'Wrong 接口类型' 
	END AS 'interface_type',
	task.NAME task_name,
	`user`.user_name user_name,
CASE ot.input_type
	WHEN 8 THEN
	'通讯标识'
	WHEN 0 THEN
	'IMEI'
	WHEN 1 THEN
	'IMEI(MD5)'
	WHEN 6 THEN
	'IDFA'
	WHEN 2 THEN
	'AndroidID'
	WHEN 4 THEN
	'MAC' ELSE 'Wrong 输入类型'
	END AS 'input_type',
	result.result_detail 
FROM
	outer_datasource ot
	LEFT JOIN task task ON ot.task_id = task.id
	LEFT JOIN outer_datasource_result result ON ot.task_id = result.task_id
	LEFT JOIN `user` ON task.user_id = `user`.user_id 
WHERE
	task.update_time >= "{start_date}" 
	AND task.update_time < "{end_date}" 
	AND task.STATUS = 3
ORDER BY
	task.update_time
"""
csv_headers = "统计时间,数据源,接口名称,项目名称,用户,输入ID类型,总调用次数,查询成功次数,计费次数,调用缓存次数"


def getReport(start_date, end_date):
    '''
    从数据库中查询指定日期的外部数据报告
    :param start_date: 开始时间 %Y-%m-%d include 
    :param end_date: 结束时间 %Y-%m-%d exclude
    :return: [[各列的值] [各列的值]]
    '''
    sql = sql_report.format(start_date=start_date, end_date=end_date)
    query_results = query(sql)
    _results = []
    for result_line in query_results:
        temp = []
        temp.append(result_line['update_time'])  # 统计时间
        temp.append(result_line['datasource'])  # 数据源
        temp.append(result_line['interface_type'])  # 接口名称
        temp.append(result_line['task_name'])  # 项目名称
        temp.append(result_line['user_name'])  # 用户
        temp.append(result_line['input_type'])  # 输入ID类型

        result_detail = result_line['result_detail']
        result_detail_json = json.loads(result_detail or "{}")  # result_detail为空的情况
        # temp.append(result_detail_json.get("total_id_count") or "error")  # 输入ID总数
        # temp.append(result_detail_json.get("uniq_id_count") or "error")  # 去重ID总数
        temp.append(result_detail_json.get("valid_id_count"))  # 合法ID总数 总调用次数

        find_id_count = result_detail_json.get("find_id_count")  # 找到ID总数
        new_id_count = result_detail_json.get("new_id_count")  # 付费ID总数
        try:
            cache_id_count = find_id_count - new_id_count  # 相减得不付费次数
        except:
            cache_id_count = "error"

        temp.append(find_id_count)  # 查询成功次数
        temp.append(new_id_count)  # 计费次数
        temp.append(cache_id_count)  # 调用缓存次数
        _results.append(temp)

    return _results


def generateCSVFile(arrays, file):
    with open(file, 'x', encoding='utf-8-sig') as fd:
        fd.write(csv_headers)
        fd.write('\n')
        for ar in arrays:
            fd.write(','.join(str(x) for x in ar))
            fd.write('\n')
    fd.close()


def main(start_date, end_date):
    '''
    根据日期执行sql获取报表 -> 生成csv文件 -> 发送邮件
    :param start_date: 
    :param end_date: 
    :return: 
    '''
    results = getReport(start_date, end_date)
    file = data_dir + "Dataplus_OuterDatasource_Report_" + start_date + "_" + end_date + ".csv"
    generateCSVFile(results, file)
    sendTextWithAttach("tianzhipeng@miaozhen.com, wangbaixin@miaozhen.com", "外部数据源使用报表",
                       "外部数据源 " + start_date + "至" + end_date + "(不含)使用情况,见附件",
                       file)


if __name__ == '__main__':
    # main('2017-02-15', '2017-02-18')
    # exit(0)

    now = datetime.now()
    now_str = now.strftime('%Y-%m-%d')

    # 发送昨天的报告
    yesterday = now - timedelta(days=1)
    yesterday_str = yesterday.strftime('%Y-%m-%d')
    main(yesterday_str, now_str)

    # 每月1号的时候需要发送上一个月的报告
    dd = now.strftime('%d')
    if dd == '01':
        last_month_day1 = yesterday.strftime('%Y-%m') + '-01'
        main(last_month_day1, now_str)
